<?php
require_once('../include/config.inc.php');
require_once('../../../vendor/autoload.php');

// 使用 PHPSpreadsheet 命名空间
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;

/**
 * 只保留字符串首尾字符，隐藏中间用*代替（两个字符时只显示第一个）
 * @param string $user_name 姓名
 * @return string 格式化后的姓名
 */
function substr_cut($user_name){
    $strlen     = mb_strlen($user_name, 'utf-8');
    $firstStr     = mb_substr($user_name, 0, 1, 'utf-8');

    return $strlen == 2 ? $firstStr . str_repeat('*', mb_strlen($user_name, 'utf-8') - 1) : $firstStr . str_repeat("*", $strlen - 2);
}

$page = isset($_GET['page']) ? intval($_GET['page']) : 0;
$size = isset($_GET['limit']) ? intval($_GET['limit']) : 0;
$username = isset($_GET['username']) ? htmlspecialchars($_GET['username']) : "";
$username = trim($username);
$nickname = isset($_GET['nickname']) ? htmlspecialchars($_GET['nickname']) : "";
$nickname = trim($nickname);
//$id= isset($_GET['id']) ? intval($_GET['id']) : "";
$mobile = isset($_GET['mobile']) ? htmlspecialchars($_GET['mobile']) : "";
$mobile = trim($mobile);
$dtime = isset($_GET['dtime']) ? htmlspecialchars($_GET['dtime']) : "";   //用车人
$dtime = trim($dtime);
$status = isset($_GET['status']) ? intval($_GET['status']) : 99;   //用户的身份
$jiang = isset($_GET['jiang']) ? intval($_GET['jiang']) : 99;   //用户的身份
$ischeck = isset($_GET['ischeck']) ? intval($_GET['ischeck']) : 99;   //是否兑换
$shopid = isset($_GET['shopid']) ? intval($_GET['shopid']) : 0;   //商家id
$uid = isset($_GET['uid']) ? intval($_GET['uid']) : 0;   //用户的身份
$start_num = ($page - 1) * $size;
$data = [];
$sql = "SELECT u.*,v.jiangpin,v.addtime as vote_time,v.id as vid,v.wxapp_code,v.ischeck,v.checktime,v.express,v.express_code,v.type,v.goodid  FROM `#@__shop_vote` as v left join `#@__user` as u on v.userid=u.id where v.jiang>0   ";


if ($ischeck < 99) {
    if ($ischeck == 0) {
        $sql .= "  and v.ischeck='0' ";
    }
    if ($ischeck == 1) {
        $sql .= "  and v.ischeck='1' ";
    }
}
if ($jiang < 99) {
    $sql .= "  and v.jiang='$jiang' ";
}

if ($shopid >0) {
    $sql .= "  and v.shopid='$shopid' ";
}


if (!empty($truename)) {
    $sql .= "  and u.username like '%$truename%'  ";
}
if (!empty($nickname)) {
    $sql .= "  and u.nickname like '%$nickname%'  ";
}
if (!empty($mobile)) {
    $sql .= "  and u.mobile like '%$mobile%'  ";
}

if ($uid > 0) {
    $sql .= "  and u.id='$uid' ";
}


if ($dtime != "") {
    $time_arr = explode("~", $dtime);
    if (is_array($time_arr) && count($time_arr) == 2) {
        $start_time = strtotime(trim($time_arr[0]));
        $end_time = strtotime(trim($time_arr[1])) + 3600 * 24 - 1;
        $sql .= " AND v.addtime>'$start_time' AND v.addtime<'$end_time' ";

    }

}



$new_item=[];


$arr_big = array(
    array('id' => 1, 'name' => '10元话费充值券', 'v' => 5),
    array('id' => 2, 'name' => '5元话费充值券', 'v' => 10),
    array('id' => 3, 'name' => '2元话费充值券', 'v' => 15),
    array('id' => 4, 'name' => '1元话费充值券', 'v' => 20),
    array('id' => 5, 'name' => '125毫升金标劲酒', 'v' => 2),
    array('id' => 6, 'name' => '125毫升蓝标劲酒', 'v' => 5),
    array('id' => 7, 'name' => '125毫升红标劲酒', 'v' => 8),
    array('id' => 8, 'name' => '购买劲酒20元优惠码', 'v' => 3),
    array('id' => 9, 'name' => '购买劲酒10元优惠码', 'v' => 10),
    array('id' => 10, 'name' => '购买劲酒5元优惠码', 'v' => 20),
    array('id' => 11, 'name' => '购买劲酒2元优惠码', 'v' => 200),
    array('id' => 12, 'name' => '购买劲酒1元优惠码', 'v' => 290),
    array('id' => 0, 'name' => '谢谢参与', 'v' => 1000),
);


$top_title="";

$dosql->Execute($sql);
while ($row = $dosql->GetArray()) {


    if($row['ischeck']==1){
        $row['check_txt']="已兑奖";
    }else{
        $row['check_txt']="";
    }


    $row['addtime'] = GetDateTime($row['vote_time']);
    $row['checktime_text'] = GetDateTime($row['checktime']);


//    $row['headimgurl'] = $row['headimgurl'] != null ? $row['headimgurl'] : '/img/adminadd.jpg';

    $row['mobile'] = is_null($row['mobile']) ? "" : $row['mobile'];
    $row['area'] = is_null($row['area']) ? "" : $row['area'];
    $row['address'] = is_null($row['address']) ? "" : $row['address'];
    if(is_numeric($row['username'])) {
        $row['username'] = " " . $row['username'];
    }

    // 隐藏兑换劵
    if ($row['wxapp_code'] != "") {
        $row['wxapp_code'] = substr_replace($row['wxapp_code'], '****', 4, 4);
    } else {
        $row['wxapp_code'] = "";
    }


    //获取商家和菜品
    $vote_good_id = $row['goodid'];
    $shopname = "";
    $goodname = "";
    $row_shop = $dosql->GetOne("select s.shopname,g.title from `#@__shop_goods` as g left join `#@__shop` as s on g.shopid=s.id where g.id='$vote_good_id' ");
    if (is_array($row_shop) && isset($row_shop['shopname'])) {
        $shopname = $row_shop['shopname'];
        $goodname = $row_shop['title'];
    }

    $row['shopname'] = $shopname;
    $row['goodname'] = $goodname;

/*    if ($row['mobile'] != "") {
        $row['mobile'] = substr_replace($row['mobile'], '****', 3, 4);
    } else {
        $row['mobile'] = "";
    }*/

/*    if ($row['username'] != "") {
        $row['username'] = substr_cut($row['username']);
    } else {
        $row['username'] = "";
    }*/


    $row_arr['data'][] = $row;


    $new_item[]=$row;

}

//echo $sql;
//var_dump($new_item);
//exit;

$cellKey = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
    'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
    'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM',
    'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'
);
//序号	省	市	区	报修单位	联系人	联系方式	设备类型	序列号	故障描述	维修进度	维修费用	报修时间	客服联系备注	完修时间	维修概要	完修工程师	备注 报修地址 报修地址说明  完修地址 完修地址说明  报修到完修距离 报修到服务站距离
$spreadsheet = new Spreadsheet();// 创建新的电子表格对象
$sheet = $spreadsheet->getActiveSheet();// 获取活动工作表
$sheet->setTitle("中奖列表");// 设置表格标题

$cellName = array(
    'id'=>array('中奖编号', 'vid', 8), //列名称，索引值，宽度
    'shopname'=>array('投票商家', 'shopname', 20), //列名称，索引值，宽度
    'goodname'=>array('助力菜品', 'goodname', 20), //列名称，索引值，宽度
    'username'=>array('姓名', 'username', 14), //列名称，索引值，宽度
    'mobile'=>array('手机号码', 'mobile', 16), //列名称，索引值，宽度
    'address'=>array('地址', 'address', 25), //列名称，索引值，宽度
    'jiangpin'=>array('奖品', 'jiangpin', 25), //列名称，索引值，宽度
    'addtime'=>array('中奖时间', 'addtime', 20),
    'check_txt'=>array('是否兑奖', 'check_txt', 14),
    'checktime_text'=>array('兑奖时间', 'checktime_text', 20),
    'wxapp_code'=>array('优惠码', 'wxapp_code', 30),
    'express'=>array('物流', 'express', 12),
    'express_code'=>array('单号', 'express_code', 20)
);



$topNumber = 2;//表头有几行占用

$styleThinBlackBorderOutline = array(
    'borders' => array(
        'allBorders' => array( //设置全部边框
            'borderStyle' => Border::BORDER_THIN //细边框
        ),
    ),
);


// 设置第一行
$spreadsheet->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);//垂直居中
$sheet->mergeCells('A1:' . $cellKey[count($cellName) - 1] . '1');
//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$sheet->setCellValue('A1', $top_title . "中奖列表\n" . $dtime);
$sheet->getStyle('A1')->getFont()->setBold(true);
$sheet->getStyle('A1')->getFont()->setSize(15);
$sheet->getRowDimension('1')->setRowHeight(50);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('A1')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);


//$objPHPExcel->getActiveSheet()->getStyle('T')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
//处理表头

$cellName_num = count($cellName);
$lastCell = $cellKey[$cellName_num - 1]; //获取最后一行的 excel 字母

$i=0;
foreach ($cellName as $k => $v) {

    $sheet->setCellValue($cellKey[$i] . $topNumber, $v[0]);//设置表头数据
    $sheet->getStyle($cellKey[$i] . $topNumber)->getFont()->setBold(true);//设置是否加粗
    $sheet->getColumnDimension($cellKey[$i])->setWidth($v[2]);//设置列宽度
    //添加边框
    $sheet->getStyle($cellKey[$i] . $topNumber)->applyFromArray($styleThinBlackBorderOutline);
    //设置字体 为10号
    $sheet->getStyle($cellKey[$i] . $topNumber)->getFont()->setSize(10);
    // 设置所有垂直居中
    $sheet->getStyle($cellKey[$i] . $topNumber)->getAlignment()
        ->setVertical(Alignment::VERTICAL_CENTER);

    // 居中
    $sheet->getStyle($cellKey[$i] . $topNumber)->getAlignment()
        ->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $i++;
}
//处理数据
foreach ($new_item as $k => $v) {
    // 当前行数值
    $now_line = $k + 1 + $topNumber;
    $ii=0;
    foreach ($cellName as $k1 => $v1) {
        $sheet->setCellValue($cellKey[$ii] . $now_line, $v[$v1[1]]);
        $sheet->getStyle($cellKey[8] . $now_line)->getFont()->getColor()->setARGB(Color::COLOR_RED);// 设置文字颜色
        $ii++;
    }

    // 设置所有垂直居中

    $sheet->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
        ->setVertical(Alignment::VERTICAL_CENTER);
    // 居中
    $sheet->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getAlignment()
        ->setHorizontal(Alignment::HORIZONTAL_CENTER);
    //添加边框
    $sheet->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->applyFromArray($styleThinBlackBorderOutline);
    $sheet->getStyle('A' . $now_line . ':' . $lastCell . $now_line)->getFont()->setSize(10);

//    $sheet->getStyle('H' . $now_line)->getAlignment()->setWrapText(true);
//    $sheet->getStyle('M' . $now_line)->getAlignment()->setWrapText(true);


}

$xlsTitle=$dtime. '中奖列表';
ob_end_clean();
//清除缓冲区,避免乱码
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=$xlsTitle.xlsx");
header('Cache-Control: max-age=0');
//header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
//header ('Cache-Control: cache, must-revalidate');
header ('Pragma: public');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');


exit
?>
